﻿//
// (C) Copyright 2009 Irantha Suwandarathna (iranthas@hotmail.com)
// All rights reserved.
//

/* Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use _in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions _in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer _in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EffiProzDB;
using EffiProzDB.Lib;


namespace System.Data.EffiProzDB
{
    class EffiProzDBMetaData
    {
        EffiProzDBConnection _cnn;
        private const string selstar = "SELECT * FROM INFORMATION_SCHEMA.";
        private const string whereTrue = " WHERE 1=1";

        public EffiProzDBMetaData(EffiProzDBConnection cnn)
        {
            _cnn = cnn;
        }

        private StringBuilder ToQueryPrefix(String t)
        {

            StringBuilder sb = new StringBuilder(255);

            return sb.Append(selstar).Append(t).Append(whereTrue);
        }

        private static String And(String id, String op, Object val)
        {
            if (val == null)
            {
                return "";
            }

            StringBuilder sb = new StringBuilder();
            bool isStr = (val is String);

            if (isStr && ((String)val).Length == 0)
            {
                return sb.Append(" AND ").Append(id).Append(" IS NULL").ToString();
            }

            String v = isStr ? Column.createSQLString((String)val)
                    : val.ToString();

            sb.Append(" AND ").Append(id).Append(' ');

            // add the escape to like if required
            if (isStr && "LIKE".Equals(op, StringComparison.OrdinalIgnoreCase))
            {
                if (v.IndexOf('_') < 0 && v.IndexOf('%') < 0)
                {

                    // then we can optimize.
                    sb.Append("=").Append(' ').Append(v);
                }
                else
                {
                    sb.Append("LIKE").Append(' ').Append(v);

                    if ((v.IndexOf("\\_") >= 0) || (v.IndexOf("\\%") >= 0))
                    {

                        // then client has requested at least one escape.
                        sb.Append(" ESCAPE '\\'");
                    }
                }
            }
            else
            {
                sb.Append(op).Append(' ').Append(v);
            }

            return sb.ToString();
        }

        private DbDataReader ExecuteSelect(String table, String where)
        {

            String select = selstar + table;

            if (where != null)
            {
                select += " WHERE " + where;
            }

            return Execute(select);
        }

        private DbDataReader Execute(String select)
        {

            DbCommand command = _cnn.CreateCommand();
            command.CommandText = select;

            return command.ExecuteReader();
        }

        private void PopulateDataTable(DataTable t, DbDataReader reader)
        {
            DataTable columnSchema = reader.GetSchemaTable();
            DataRow row;
            int columnCount = columnSchema.Rows.Count;
            for (int i = 0; i < columnCount; i++)
            {
                row = columnSchema.Rows[i];
                t.Columns.Add((string)row[SchemaTableColumn.ColumnName], (Type)row[SchemaTableColumn.DataType]);
            }

            t.BeginLoadData();
            while (reader.Read())
            {
                row = t.NewRow();
                for (int i = 0; i < columnCount; i++)
                {
                    row[i] = reader.GetValue(i);
                }
                t.Rows.Add(row);
            }

            t.AcceptChanges();
            t.EndLoadData();
        }


        public DataTable GetTables(String catalogPattern, string schemaPattern, string tableNamePattern, String types)
        {
            
            DbDataReader reader;

            if (String.IsNullOrEmpty(tableNamePattern)
               || (types != null && types.Length == 0))
            {
                reader = ExecuteSelect("SYSTEM_TABLES", "0=1");
            }

            StringBuilder select = ToQueryPrefix("SYSTEM_TABLES").Append(And("TABLE_CAT", "LIKE",
                                    catalogPattern)).Append(And("TABLE_SCHEM", "LIKE",
                                    schemaPattern)).Append(And("TABLE_NAME",
                                        "LIKE", tableNamePattern));//.Append(" AND TABLE_TYPE != 'VIEW'");
            if (types != null)
            {
                select.Append(" AND TABLE_TYPE IN (").Append(types).Append(')');
            }

            reader = Execute(select.ToString());         

            DataTable tbl = new DataTable("Tables");

            PopulateDataTable(tbl, reader);

            return tbl;
        }

        public DataTable GetSchemas()
        {
            DbDataReader reader;

            reader = ExecuteSelect("SYSTEM_SCHEMAS", null);

            DataTable tbl = new DataTable("Schemas");
            PopulateDataTable(tbl, reader);

            return tbl;
        }

        public DataTable GetTableTypes()
        {
            DbDataReader reader;

            reader = ExecuteSelect("SYSTEM_TABLETYPES", null);

            DataTable tbl = new DataTable("TableTypes");
            PopulateDataTable(tbl, reader);

            return tbl;
        }

        public DataTable GetTypes()
        {
            DbDataReader reader;

            reader = ExecuteSelect("SYSTEM_TYPEINFO", null);

            DataTable tbl = new DataTable("Types");
            PopulateDataTable(tbl, reader);

            return tbl;
        }

        public DataTable GetProcedureColumns(String schemaPattern,
                                         String procedureNamePattern,
                                         String columnNamePattern)
        {
            DbDataReader reader;

            if (String.IsNullOrEmpty(procedureNamePattern) && String.IsNullOrEmpty(columnNamePattern))
            {
                reader = ExecuteSelect("SYSTEM_PROCEDURECOLUMNS", "0=1");
            }


            StringBuilder select =
                ToQueryPrefix("SYSTEM_PROCEDURECOLUMNS").Append(
            And("PROCEDURE_SCHEM", "LIKE", schemaPattern)).Append(
            And("PROCEDURE_NAME", "LIKE", procedureNamePattern)).Append(
            And("COLUMN_NAME", "LIKE", columnNamePattern));

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("ProcedureColumns");

            PopulateDataTable(tbl, reader);


            return tbl;
        }



        public DataTable GetProcedures(String schemaPattern,
                                   String procedureNamePattern)
        {
            DbDataReader reader;

            if (String.IsNullOrEmpty(procedureNamePattern))
            {
                reader = ExecuteSelect("SYSTEM_PROCEDURES", "0=1");
            }


            StringBuilder select =
                ToQueryPrefix("SYSTEM_PROCEDURES").Append(
            And("PROCEDURE_SCHEM", "LIKE", schemaPattern)).Append(
            And("PROCEDURE_NAME", "LIKE", procedureNamePattern));

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("Procedures");

            PopulateDataTable(tbl, reader);
     

            return tbl;
        }

        public DataTable GetColumns(String catalogPattern, String schemaPattern,
                                String tableNamePattern,
                                String columnNamePattern)
        {
            DbDataReader reader;

            if (String.IsNullOrEmpty(tableNamePattern) && String.IsNullOrEmpty(columnNamePattern))
            {
                reader =  ExecuteSelect("SYSTEM_COLUMNS", "0=1");
            }                      

            StringBuilder select =
                ToQueryPrefix("SYSTEM_COLUMNS").Append(
                And("TABLE_CAT", "LIKE", catalogPattern)).Append(
                And("TABLE_SCHEM", "LIKE", schemaPattern)).Append(
                And("TABLE_NAME", "LIKE", tableNamePattern)).Append(
                And("COLUMN_NAME", "LIKE", columnNamePattern));

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("Columns");

            PopulateDataTable(tbl, reader);

            DataTable tblpk = GetPrimaryKeys(catalogPattern, schemaPattern, tableNamePattern);
            Dictionary<string,string> pkmap = new Dictionary<string,string>();
            for (int i = 0; i < tblpk.Rows.Count; i++)
            {
                string col = (string)tblpk.Rows[i]["COLUMN_NAME"];
                pkmap.Add(col, col);
            }

            tbl.Columns.Add(new DataColumn("PRIMARY_KEY",typeof(bool)));

            for (int i = 0; i < tbl.Rows.Count; i++)
            {
               string col = (string)tbl.Rows[i]["COLUMN_NAME"];
               if (pkmap.ContainsKey(col))
                   tbl.Rows[i]["PRIMARY_KEY"] = true;
               else
                   tbl.Rows[i]["PRIMARY_KEY"] = false;
            }
            tbl.AcceptChanges();

            return tbl;
        }

        public DataTable GetTablePrivileges(String schemaPattern,
                                        String tableNamePattern)
        {
            DbDataReader reader;

            if (String.IsNullOrEmpty(tableNamePattern) )
            {
                reader = ExecuteSelect("SYSTEM_TABLEPRIVILEGES", "0=1");
            }


            StringBuilder select =
                ToQueryPrefix("SYSTEM_TABLEPRIVILEGES").Append(
            And("TABLE_SCHEM", "LIKE", schemaPattern)).Append(
            And("TABLE_NAME", "LIKE", tableNamePattern));

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("TablePrivileges");

            PopulateDataTable(tbl, reader);

            return tbl;
        }


        public DataTable GetForeignKeys(String catalogPattern, String schemaPattern,
                                String tableNamePattern,
                                String namePattern)
        {
            DbDataReader reader;

            //if (primaryTable == null || foreignTable == null)
            //{
            //    throw EffiProzDBException.GetException(Trace.INVALID_ADO_ARGUMENT);
            //}

            StringBuilder query = new StringBuilder();
            query.Append("SELECT DISTINCT FKTABLE_CAT,FKTABLE_SCHEM,FKTABLE_NAME,FK_NAME"+
            ", PKTABLE_CAT,PKTABLE_SCHEM,PKTABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_CROSSREFERENCE ");

       
            StringBuilder select =
            query.Append(whereTrue).Append(
            And("FKTABLE_CAT", "LIKE", catalogPattern)).Append(
            And("FKTABLE_SCHEM", "LIKE", schemaPattern)).Append(
            And("FKTABLE_NAME", "LIKE", tableNamePattern)).Append(
            And("FK_NAME", "LIKE", namePattern));

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("ForeignKeys");

            PopulateDataTable(tbl, reader);

            return tbl;
        }

        public DataTable GetForeignKeyColumns(String catalogPattern, String schemaPattern,
                               String tableNamePattern,
                               String namePattern , string columPattern)
        {
            DbDataReader reader;

            //if (primaryTable == null || foreignTable == null)
            //{
            //    throw EffiProzDBException.GetException(Trace.INVALID_ADO_ARGUMENT);
            //}


            StringBuilder select =
            ToQueryPrefix("SYSTEM_CROSSREFERENCE").Append(
            And("FKTABLE_CAT", "LIKE", catalogPattern)).Append(
            And("FKTABLE_SCHEM", "LIKE", schemaPattern)).Append(
            And("FKTABLE_NAME", "LIKE", tableNamePattern)).Append(
            And("FK_NAME", "LIKE", namePattern)).Append(
            And("FKCOLUMN_NAME", "LIKE", columPattern));

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("ForeignKeyColumns");

            PopulateDataTable(tbl, reader);

            return tbl;
        }

       

        public DataTable GetImportedKeys( String schema,
                                     String table)
        {
            DbDataReader reader;

            //if (table == null)
            //{
            //    throw EffiProzDBException.GetException(Trace.INVALID_ADO_ARGUMENT);
            //}

           
            StringBuilder select =
            ToQueryPrefix("SYSTEM_CROSSREFERENCE").Append(
            And("FKTABLE_SCHEM", "=", schema)).Append(
            And("FKTABLE_NAME", "=", table)).Append(
            " ORDER BY PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ");

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("ImportedKeys");

            PopulateDataTable(tbl, reader);

            return tbl;
        }

        public DataTable GetExportedKeys(String schema,
                                   String table)
        {
            DbDataReader reader;

            //if (table == null)
            //{
            //    throw EffiProzDBException.GetException(Trace.INVALID_ADO_ARGUMENT);
            //}


            StringBuilder select =
            ToQueryPrefix("SYSTEM_CROSSREFERENCE").Append(And("PKTABLE_SCHEM", "=",
                                          schema)).Append(And("PKTABLE_NAME",
                                              "=", table));
            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("ExportedKeys");

            PopulateDataTable(tbl, reader);

            return tbl;
        }

        public DataTable GetIndexes(String catalog, String schema, String table,String index, string _unique)
        {
            DbDataReader reader;

            if (table == null)
            {
                throw EffiProzDBException.GetException(Trace.INVALID_ADO_ARGUMENT);
            }

         
            StringBuilder query = new StringBuilder();
            query.Append("SELECT DISTINCT TABLE_CAT,TABLE_SCHEM,TABLE_NAME,NON_UNIQUE, INDEX_QUALIFIER,INDEX_NAME FROM INFORMATION_SCHEMA.SYSTEM_INDEXINFO ");


             StringBuilder select = null;

             if (!String.IsNullOrEmpty(_unique))
             {
                 bool? nu = (Convert.ToBoolean(_unique)) ? (bool?)false : null;

                 select = query.Append(whereTrue).Append(And("TABLE_CAT", "LIKE",
                                        catalog)).Append(And("TABLE_SCHEM", "LIKE",
                                        schema)).Append(And("TABLE_NAME", "LIKE",
                                            table)).Append(And("INDEX_NAME", "LIKE",
                                            index)).Append(And("NON_UNIQUE", "=",
                                                nu));
             }
             else
             {

                 select = query.Append(whereTrue).Append(And("TABLE_CAT", "LIKE",
                                        catalog)).Append(And("TABLE_SCHEM", "LIKE",
                                        schema)).Append(And("TABLE_NAME", "LIKE",
                                            table)).Append(And("INDEX_NAME", "LIKE",
                                            index));
             }

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("Indexes");

            PopulateDataTable(tbl, reader);

            return tbl;
        }


        public DataTable GetIndexColumns(String catalog, String schema, String table, string index , string name,
            string _unique)
        {
            DbDataReader reader;

            //if (table == null)
            //{
            //    throw EffiProzDBException.GetException(Trace.INVALID_ADO_ARGUMENT);
            //}

            StringBuilder select = null;

            if (!String.IsNullOrEmpty(_unique))
            {
                bool? nu = (Convert.ToBoolean(_unique)) ? (bool?)false : null;

                select =
                ToQueryPrefix("SYSTEM_INDEXINFO").Append(And("TABLE_CAT", "LIKE",
                                        catalog)).Append(And("TABLE_SCHEM", "LIKE",
                                        schema)).Append(And("TABLE_NAME", "LIKE",
                                        table)).Append(And("INDEX_NAME", "LIKE",
                                            index)).Append(And("COLUMN_NAME", "LIKE",
                                            name)).Append(And("NON_UNIQUE", "=",
                                                nu));
            }
            else
            {
                select =
               ToQueryPrefix("SYSTEM_INDEXINFO").Append(And("TABLE_CAT", "LIKE",
                                       catalog)).Append(And("TABLE_SCHEM", "LIKE",
                                       schema)).Append(And("TABLE_NAME", "LIKE",
                                       table)).Append(And("INDEX_NAME", "LIKE",
                                           index)).Append(And("COLUMN_NAME", "LIKE",
                                           name));
            }


            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("IndexeColumns");

            PopulateDataTable(tbl, reader);

            return tbl;
        }

        public DataTable GetPrimaryKeys(String catalog, String schema,
                                    String table)
        {
            DbDataReader reader;

            if (table == null)
            {
                throw EffiProzDBException.GetException(Trace.INVALID_ADO_ARGUMENT);
            }
           
            StringBuilder select =
                ToQueryPrefix("SYSTEM_PRIMARYKEYS").Append(And("TABLE_CAT", "LIKE",
                                    catalog)).Append(And("TABLE_SCHEM", "LIKE",
                                         schema)).Append(And("TABLE_NAME", "LIKE",
                                             table));

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("PrimaryKeys");

            PopulateDataTable(tbl, reader);

            return tbl;
        }

        public DataTable GetViews(String catalog, String schema,   String view)
        {
            DbDataReader reader;

            //if (view == null)
            //{
            //    throw EffiProzDBException.GetException(Trace.INVALID_ADO_ARGUMENT);
            //}

            StringBuilder select =
                ToQueryPrefix("SYSTEM_VIEWS").Append(And("TABLE_CATALOG", "LIKE",
                                    catalog)).Append(And("TABLE_SCHEMA", "LIKE",
                                         schema)).Append(And("TABLE_NAME", "LIKE",
                                             view));

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("Views");

            PopulateDataTable(tbl, reader);

            return tbl;
        }



        public DataTable GetTriggers(String catalog, String schema,String table,
                                   String trigger)
        {
            DbDataReader reader;

            //if (trigger == null)
            //{
            //    throw EffiProzDBException.GetException(Trace.INVALID_ADO_ARGUMENT);
            //}

            StringBuilder select =
                ToQueryPrefix("SYSTEM_TRIGGERS").Append(And("TABLE_CAT", "LIKE",
                                         catalog)).Append(And("TABLE_SCHEM", "LIKE",
                                         schema)).Append(And("TABLE_NAME", "LIKE",
                                         schema)).Append(And("TRIGGER_NAME", "LIKE",
                                             trigger));

            reader = Execute(select.ToString());

            DataTable tbl = new DataTable("Triggers");

            PopulateDataTable(tbl, reader);

            return tbl;
        }

        /// <summary>
        /// Builds a DataSourceInformation datatable
        /// </summary>
        /// <returns>DataTable</returns>
        private DataTable GetDataSourceInformation()
        {
            DataTable tbl = new DataTable("DataSourceInformation");
            DataRow row;

            tbl.Locale = System.Globalization.CultureInfo.InvariantCulture;
            tbl.Columns.Add(DbMetaDataColumnNames.CompositeIdentifierSeparatorPattern, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductName, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersion, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.DataSourceProductVersionNormalized, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.GroupByBehavior, typeof(int));
            tbl.Columns.Add(DbMetaDataColumnNames.IdentifierPattern, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.IdentifierCase, typeof(int));
            tbl.Columns.Add(DbMetaDataColumnNames.OrderByColumnsInSelect, typeof(bool));
            tbl.Columns.Add(DbMetaDataColumnNames.ParameterMarkerFormat, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.ParameterMarkerPattern, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.ParameterNameMaxLength, typeof(int));
            tbl.Columns.Add(DbMetaDataColumnNames.ParameterNamePattern, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierPattern, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.QuotedIdentifierCase, typeof(int));
            tbl.Columns.Add(DbMetaDataColumnNames.StatementSeparatorPattern, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.StringLiteralPattern, typeof(string));
            tbl.Columns.Add(DbMetaDataColumnNames.SupportedJoinOperators, typeof(int));

            tbl.BeginLoadData();

            row = tbl.NewRow();
            row.ItemArray = new object[] {
        null,
        "EffiProz",
        "Alpha-Testing",
        "Alpha-Testing",
        3,
        @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)",
        1,
        false,
        "{0}",
        @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)",
        255,
        @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)",
        @"(([^\[]|\]\])*)",
        1,
        ";",
        @"'(([^']|'')*)'",
        15
      };
            tbl.Rows.Add(row);

            tbl.AcceptChanges();
            tbl.EndLoadData();

            return tbl;
        }


        private static DataTable GetReservedWords()
        {
            DataTable tbl = new DataTable("MetaDataCollections");

            tbl.Locale = System.Globalization.CultureInfo.InvariantCulture;
            tbl.Columns.Add("ReservedWord", typeof(string));
            tbl.Columns.Add("MaximumVersion", typeof(string));
            tbl.Columns.Add("MinimumVersion", typeof(string));

            string keyWords = "BEFORE,BIGINT,BINARY,CACHED,DATE,TIME,"
              + "LIMIT,LONGVARBINARY,LONGVARCHAR,OBJECT,OTHER,SAVEPOINT,"
              + "TEMP,TEXT,TOP,TRIGGER,TINYINT,VARBINARY,VARCHAR_IGNORECASE";

            tbl.BeginLoadData();
            DataRow row;
            foreach (string word in keyWords.Split(new char[] { ',' }))
            {
                row = tbl.NewRow();
                row[0] = word;
                tbl.Rows.Add(row);
                row[1] = DBNull.Value; row[2] =DBNull.Value;

            }

            tbl.AcceptChanges();
            tbl.EndLoadData();

            return tbl;
        }

        public DataTable GetDataTypes()
        {
            DataTable tbl = new DataTable("DataTypes");

            tbl.Locale = System.Globalization.CultureInfo.InvariantCulture;
            tbl.Columns.Add("TypeName", typeof(String));
            tbl.Columns.Add("ProviderDbType", typeof(int));
            tbl.Columns.Add("ColumnSize", typeof(long));
            tbl.Columns.Add("CreateFormat", typeof(String));
            tbl.Columns.Add("CreateParameters", typeof(String));
            tbl.Columns.Add("DataType", typeof(String));
            tbl.Columns.Add("IsAutoIncrementable", typeof(bool));
            tbl.Columns.Add("IsBestMatch", typeof(bool));
            tbl.Columns.Add("IsCaseSensitive", typeof(bool));
            tbl.Columns.Add("IsFixedLength", typeof(bool));
            tbl.Columns.Add("IsFixedPrecisionScale", typeof(bool));
            tbl.Columns.Add("IsLong", typeof(bool));
            tbl.Columns.Add("IsNullable", typeof(bool));
            tbl.Columns.Add("IsSearchable", typeof(bool));
            tbl.Columns.Add("IsSearchableWithLike", typeof(bool));
            tbl.Columns.Add("IsLiteralSupported", typeof(bool));
            tbl.Columns.Add("LiteralPrefix", typeof(String));
            tbl.Columns.Add("LiteralSuffix", typeof(String));
            tbl.Columns.Add("IsUnsigned", typeof(bool));
            tbl.Columns.Add("MaximumScale", typeof(short));
            tbl.Columns.Add("MinimumScale", typeof(short));
            tbl.Columns.Add("IsConcurrencyType", typeof(bool));


            DbDataReader reader;          
            StringBuilder select =
                ToQueryPrefix("SYSTEM_ALLTYPEINFO").Append(And("AS_TAB_COL", "=",
                                    true));
            reader = Execute(select.ToString());

            DataRow row;
            tbl.BeginLoadData();
            while (reader.Read())
            {
                row = tbl.NewRow();
                row["TypeName"] = reader["TYPE_NAME"];
                row["ProviderDbType"] = reader["DATA_TYPE"];
                row["ColumnSize"] = reader["PRECISION"];
                row["CreateFormat"] = reader["TYPE_NAME"];
                row["CreateParameters"] = DBNull.Value;
                row["IsAutoIncrementable"] = reader["AUTO_INCREMENT"];
                row["IsBestMatch"] = true;
                row["IsFixedLength"] = reader["FIXED_PREC_SCALE"];
                row["IsFixedPrecisionScale"] = reader["FIXED_PREC_SCALE"];
                row["IsLong"] = false;
                row["IsNullable"] = Convert.ToInt32(reader["NULLABLE"]) !=0;
                row["IsSearchable"] = Convert.ToInt32(reader["SEARCHABLE"]) != 0;
                row["IsLiteralSupported"] = DBNull.Value;
                row["LiteralPrefix"] = reader["LITERAL_PREFIX"];
                row["LiteralSuffix"] = reader["LITERAL_SUFFIX"];
                row["IsUnsigned"] = reader["UNSIGNED_ATTRIBUTE"];
                row["MaximumScale"] = reader["MAXIMUM_SCALE"];
                row["MinimumScale"] = reader["MINIMUM_SCALE"];
                row["IsConcurrencyType"] = false;

                tbl.Rows.Add(row);
            }

            tbl.AcceptChanges();
            tbl.EndLoadData();

            return tbl;
        }



        public DataTable GetMetaDataCollections()
        {
            DataTable tbl = new DataTable("MetaDataCollections");

            tbl.Locale = System.Globalization.CultureInfo.InvariantCulture;
            tbl.Columns.Add("CollectionName", typeof(string));
            tbl.Columns.Add("NumberOfRestrictions", typeof(string));
            tbl.Columns.Add("NumberOfIdentifierParts", typeof(string));          

            tbl.BeginLoadData();

            DataRow row;

            row = tbl.NewRow();
            row[0] = "Schemas";
            row[1] = 1;
            tbl.Rows.Add(row);

            row = tbl.NewRow();
            row[0] = "Tables";
            row[1] = 4;
            row[2] = 3;         
            tbl.Rows.Add(row);

            row = tbl.NewRow();
            row[0] = "Columns";
            row[1] = 4;
            row[2] = 4;
            tbl.Rows.Add(row);

            row = tbl.NewRow();
            row[0] = "ReservedWords";
            row[1] = 0;
            row[2] = 0;
            tbl.Rows.Add(row);

            row = tbl.NewRow();
            row[0] = "Views";
            row[1] = 3;
            row[2] = 3;
            tbl.Rows.Add(row);

            row = tbl.NewRow();
            row[0] = "DataTypes";
            row[1] = 0;
            row[2] = 0;
            tbl.Rows.Add(row);

            row = tbl.NewRow();
            row[0] = "Indexes";
            row[1] = 5;
            row[2] = 4;
            tbl.Rows.Add(row);

            row = tbl.NewRow();
            row[0] = "IndexColumns";
            row[1] = 6;
            row[2] = 5;
            tbl.Rows.Add(row);

            row = tbl.NewRow();
            row[0] = "ForeignKeys";
            row[1] = 4;
            row[2] = 4;
            tbl.Rows.Add(row);

            row = tbl.NewRow();
            row[0] = "ForeignKeyColumns";
            row[1] = 5;
            row[2] = 5;
            tbl.Rows.Add(row);
         

            tbl.AcceptChanges();
            tbl.EndLoadData();

            return tbl;
        }

        public DataTable GetMetaData(string collectionName, string[] restrictionValues)
        {
           
            string[] parms = new string[6];

            if (restrictionValues == null) restrictionValues = new string[0];
            restrictionValues.CopyTo(parms, 0);

            switch (collectionName.ToUpper(System.Globalization.CultureInfo.InvariantCulture))
            {
                case "METADATACOLLECTIONS":
                    return GetMetaDataCollections();
                case "SCHEMAS":
                    return GetSchemas();
                case "TABLES":
                    return GetTables(parms[0], parms[1], parms[2], parms[3]);
                case "COLUMNS":
                case "TABLECOLUMNS":
                    return GetColumns(parms[0], parms[1], parms[2],parms[3]);
                case "INDEXES":
                    return GetIndexes(parms[0], parms[1],parms[2],parms[3], parms[4]);
                case "INDEXCOLUMNS":
                    return GetIndexColumns(parms[0], parms[1], parms[2],parms[3], parms[4],parms[5]);
                case "TRIGGERS":
                    return GetTriggers(parms[0], parms[1], parms[2], parms[3]);
                case "EXPORTEDKEYS":
                    return GetExportedKeys(parms[0], parms[1]);
                case "IMPORTEDKEYS":
                    return GetImportedKeys(parms[0], parms[1]);
                case "VIEWS":
                    return GetViews(parms[0], parms[1],parms[2]);
                case "FOREIGNKEYS":
                    return GetForeignKeys(parms[0], parms[1], parms[2], parms[3]);
                case "FOREIGNKEYCOLUMNS":
                    return GetForeignKeyColumns(parms[0], parms[1], parms[2], parms[3], parms[4]);
                case "TYPES":
                    return GetTypes();
                case "TABLETYPES":
                    return GetTableTypes();
                case "TABLEPRIVILEGES":
                    return GetTablePrivileges(parms[0], parms[1]);
                case "PROCEDURES":
                    return GetProcedures(parms[0], parms[1]);
                case "PROCEDUREPARAMETERS":
                    return GetProcedureColumns(parms[0], parms[1], parms[2]);
                case "PRIMARYKEYS":
                    return GetPrimaryKeys(parms[0], parms[1], parms[2]);
                case "DATASOURCEINFORMATION":
                    return GetDataSourceInformation();
                case "RESERVEDWORDS":
                    return GetReservedWords();
                case "DATATYPES":
                    return GetDataTypes();
            }
            throw new NotSupportedException();
        }      
    }
}
